﻿Imports System.Data.SqlClient

Public Class frmModuloOptocontrol
    Private mstrCondicion As String
    Public WriteOnly Property Condicion() As String
        Set(ByVal value As String)
            mstrCondicion = value
        End Set
    End Property
    Private Sub frmModuloOptocontrol_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        toolMenu.Cursor = Cursors.Hand
        toolMenu.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        datFecha.Value = Now()
        txtAutobus.Clear()
        txtRol.Clear()


        CargaDias()
        SeleccionaDia()

        dgOptocontrol.Rows.Clear()
        dgRecaudacion.Rows.Clear()


        cargaAutobuses()
    End Sub
    Sub SeleccionaDia()
        Dim Dia As String = ""
        Select Case datFecha.Value.DayOfWeek
            Case 0
                Dia = "Domingo"
            Case 1
                Dia = "Lunes"
            Case 2
                Dia = "Martes"
            Case 3
                Dia = "Miercoles"
            Case 4
                Dia = "Jueves"
            Case 5
                Dia = "Viernes"
            Case 6
                Dia = "Sabado"
        End Select

        lstTipoDia.Text = Dia

    End Sub
    Sub CargaRoles()
        Dim Dia As String = lstTipoDia.Text
        lstRoles.Items.Clear()

        Dim strNombre As String
        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection
            strNombre = UCase(txtRol.Text.Trim)
            strNombre = "%" & strNombre.Replace(" ", "%") & "%"

            strSql = "select Nombre from RolesDescuentos where UPPER(TipoDia)='" & UCase(Dia) & "' AND UPPER(Nombre) like '" & strNombre & "' " & mstrCondicion & " ORDER BY Nombre"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader
            Dim primero As Boolean
            primero = True

            Do While rdBuscar.Read()
                Me.lstRoles.Items.Add(rdBuscar("Nombre").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try
    End Sub
    Sub cargaAutobuses()
        lstAutobuses.Items.Clear()

        Dim strNombre As String
        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection
            strNombre = UCase(txtAutobus.Text.Trim)
            strNombre = "%" & strNombre.Replace(" ", "%") & "%"

            strSql = "select NoEconomico from Autobuses where Estatus=1 AND UPPER(NoEconomico) like '" & strNombre & "' " & mstrCondicion & " ORDER BY NoEconomico"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader
            Dim primero As Boolean
            primero = True

            Do While rdBuscar.Read()
                Me.lstAutobuses.Items.Add(rdBuscar("NoEconomico").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try

    End Sub
    Sub CargaHistoricoRecaudacion()
        Dim Cod_Vehiculo As String = BuscarStrDatoCitra("SELECT Cod_Vehiculo FROM Autobuses WHERE NoEconomico='" & lstAutobuses.Text & "'")
        If Cod_Vehiculo = "" Then
            MsgBox("El autobus no tiene esta ligado a ningún autobus de OptoControl", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        dgOptocontrol.Rows.Clear()

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = OptocontrolConnection

        Dim Fecha As String = datFecha.Value.ToString("MM/dd/yyyy")

        Dim strSql As String = ""
        strSql = "Select FECHA_HORA_INICIO,FECHA_HORA_FIN,MINUTOS,BLOQUEOS,H.KILOMETROS,PASAJEROS,MONTO,TARIFA_PROMEDIO,C.NOMBRE,C.APELLIDO1,C.APELLIDO2,COD_RUTA"
        strSql = strSql & " From HISTORICO_RECAUDACION H INNER JOIN VEHICULO V ON H.ID_VEHICULO=V.ID_VEHICULO INNER JOIN RUTA R ON H.ID_RUTA=R.ID_RUTA INNER JOIN CONDUCTOR C ON H.ID_CONDUCTOR=C.ID_CONDUCTOR WHERE V.COD_VEHICULO='" & Cod_Vehiculo & "' AND CONVERT(VARCHAR,H.FECHA,101)='" & Fecha & "'"
        strSql = strSql & " ORDER BY FECHA_HORA_INICIO"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            dgOptocontrol.Rows.Add(rdBuscar("FECHA_HORA_INICIO"), rdBuscar("FECHA_HORA_FIN"), rdBuscar("MINUTOS"), rdBuscar("BLOQUEOS") / 6, rdBuscar("KILOMETROS"), rdBuscar("PASAJEROS"), rdBuscar("MONTO"), rdBuscar("TARIFA_PROMEDIO"), rdBuscar("APELLIDO1") & " " & rdBuscar("APELLIDO2") & " " & rdBuscar("NOMBRE"), rdBuscar("COD_RUTA"))
        Loop

        dgOptocontrol.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(0).DefaultCellStyle.Format = "HH:mm:ss"
        dgOptocontrol.Columns(1).DefaultCellStyle.Format = "HH:mm:ss"
        dgOptocontrol.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgOptocontrol.Columns(6).DefaultCellStyle.Format = "N2"
    End Sub

    Private Sub txtAutobus_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAutobus.TextChanged
        cargaAutobuses()

    End Sub
    Sub CargaDias()
        lstTipoDia.Items.Clear()

        Dim cnconn As New SqlConnection
        Dim strSql As String = ""
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnconn.ConnectionString = CitraConnection
           
            strSql = "SELECT TipoDia FROM RolesDescuentos GROUP BY TipoDia"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader
            Dim primero As Boolean
            primero = True

            Do While rdBuscar.Read()
                Me.lstTipoDia.Items.Add(rdBuscar("TipoDia").ToString.Trim)
            Loop
        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try
    End Sub

    Private Sub datFecha_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datFecha.ValueChanged
        SeleccionaDia()
        CargaRoles()

        Dim Bus As String = lstAutobuses.Text
        If Len(Bus) > 0 Then
            CargaHistoricoRecaudacion()
            CargaRecaudacionDia()

        End If
    End Sub

    Private Sub lstAutobuses_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstAutobuses.DoubleClick
        Dim Bus As String = lstAutobuses.Text
        If Len(Bus) > 0 Then
            CargaHistoricoRecaudacion()
            CargaRecaudacionDia()

        Else
            MsgBox("No se ha seleccionado ningun autobus", MsgBoxStyle.Exclamation, "Aviso")
        End If
    End Sub

    Private Sub lstAutobuses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstAutobuses.SelectedIndexChanged

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Autobus As String = lstAutobuses.Text
        Dim Rol As String = lstRoles.Text

        If Len(Autobus) = 0 Then
            MsgBox("No se ha seleccionado ningun autobus", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        If Len(Rol) = 0 Then
            MsgBox("No se ha seleccionado ningun rol", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        Dim R As New clsRecaudacion
        R.IdAutobus = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE NoEconomico='" & lstAutobuses.Text & "'")
        R.IdServicio = BuscarIntDatoCitra("SELECT IdServicio FROM RolesDescuentos WHERE Nombre='" & lstRoles.Text & "'")
        R.IdOperador = BuscarIntDatoCitra("SELECT IdOperador FROM OperadoresGlobal WHERE Nombre='" & dgOptocontrol.CurrentRow.Cells(8).Value & "'")
        R.Fecha = datFecha.Value
        R.Pasajeros = dgOptocontrol.CurrentRow.Cells(5).Value
        R.Monto = dgOptocontrol.CurrentRow.Cells(6).Value

        Dim PorcentajeMarcas As Double = BuscarDblDatoCitra("SELECT PorcentajeMarcas FROM RolesDescuentos WHERE IdServicio=" & R.IdServicio)
        R.MarcasDescontadas = R.Pasajeros * (PorcentajeMarcas / 100)
        R.MarcasRedondeadas = Int(R.MarcasDescontadas)

        Dim PorcentajeDescuento As Double = BuscarDblDatoCitra("SELECT PorcentajeDescuento FROM RolesDescuentos WHERE IdServicio=" & R.IdServicio)
        Dim Tarifa As Double = dgOptocontrol.CurrentRow.Cells(7).Value
        R.MontoDeducciones = (R.MarcasDescontadas * Tarifa) * (PorcentajeDescuento / 100)
        R.MontoMenosDeducciones = R.Monto - R.MontoDeducciones
        R.Insertar()

        CargaRecaudacionDia()

    End Sub
    Sub CargaRecaudacionDia()
        Dim IdAutobus As Integer = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE NoEconomico='" & lstAutobuses.Text & "'")

        dgRecaudacion.Rows.Clear()

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim Fecha As String = datFecha.Value.ToString("MM/dd/yyyy")

        Dim strSql As String = ""
        strSql = "Select * "
        strSql = strSql & " From Recaudacion WHERE IdAutobus=" & IdAutobus & " AND CONVERT(VARCHAR,FECHA,101)='" & Fecha & "'"
        strSql = strSql & " ORDER BY IdRecaudacion"

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            dgRecaudacion.Rows.Add(rdBuscar("IdRecaudacion"), rdBuscar("Pasajeros"), rdBuscar("Monto"), rdBuscar("MarcasRedondeadas"), rdBuscar("MontoDeducciones"), rdBuscar("MontoMenosDeducciones"), BuscarStrDatoCitra("SELECT Nombre FROM RolesDescuentos WHERE IdServicio=" & rdBuscar("IdServicio")))
        Loop

        dgRecaudacion.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgRecaudacion.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgRecaudacion.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgRecaudacion.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgRecaudacion.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgRecaudacion.Columns(3).DefaultCellStyle.Format = "N2"
        dgRecaudacion.Columns(4).DefaultCellStyle.Format = "N2"
        dgRecaudacion.Columns(5).DefaultCellStyle.Format = "N2"
    End Sub

    Private Sub txtRol_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRol.TextChanged
        CargaRoles()

    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Reporte()
    End Sub
    Sub Reporte()
        Dim xl As Object
        Dim wb As Object
        Dim i As Integer
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 5

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strFecha1 As String = datFecha.Value.ToString("MM/dd/yyyy")

        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection
        Dim strSql As String = ""
        cnconn.Open()
        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnconn

        strSql = " SELECT NoEconomico,SUM(Pasajeros) as Pasajeros,SUM(Monto) AS Monto,SUM(MarcasRedondeadas) as Marcas,SUM(MontoDeducciones) as Deducciones,SUM(MontoMenosDeducciones) as Neto " & _
                 " FROM Recaudacion R INNER JOIN Autobuses A ON R.IdAutobus=A.IdAutobus  " & _
                 " WHERE convert(varchar,fecha,101)='" & strFecha1 & "'" & _
                 " GROUP BY NoEconomico " & _
                 " ORDER BY NoEconomico"

        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        rdBuscar = cmdBuscar.ExecuteReader
        Dim strCamion As String = "?"

        Dim totalUB As Double = 0
        Dim TotalL As Double = 0
        Dim totalV As Double = 0

        Dim totalUBG As Double = 0
        Dim TotalLG As Double = 0
        Dim totalVG As Double = 0

        wb.SHEETS(1).columns(2).columnwidth = 10
        wb.SHEETS(1).columns(3).columnwidth = 15
        wb.SHEETS(1).columns(4).columnwidth = 15
        wb.SHEETS(1).columns(5).columnwidth = 30
        wb.SHEETS(1).columns(6).columnwidth = 10
        wb.SHEETS(1).columns(7).columnwidth = 10
        wb.SHEETS(1).columns(8).columnwidth = 10
        wb.SHEETS(1).columns(9).columnwidth = 12

        'For i = 5 To 8
        wb.SHEETS(1).columns(5).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(8).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(9).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(10).NumberFormat = "#,##0.0"
        wb.SHEETS(1).columns(11).NumberFormat = "#,##0.0"
        'Next
        'wb.SHEETS(1).columns(8).NumberFormat = "#,##0.00"

        wb.SHEETS(1).cells(rng, 2).value = "No. Economico"
        wb.SHEETS(1).cells(rng, 3).value = "Conductor"
        wb.SHEETS(1).cells(rng, 4).value = "Pasajeros"
        wb.SHEETS(1).cells(rng, 5).value = "Monto Bruto"
        wb.SHEETS(1).cells(rng, 6).value = "Marcas descontadas"
        wb.SHEETS(1).cells(rng, 7).value = "Pasajeros a cobrar"
        wb.SHEETS(1).cells(rng, 8).value = "Total deducciones"
        wb.SHEETS(1).cells(rng, 9).value = "Total a liquidar"
        wb.SHEETS(1).cells(rng, 10).value = "Total Optocontrol"
        wb.SHEETS(1).cells(rng, 11).value = "Diferencia"

        For i = 2 To 11
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).CELLS(rng, i).HorizontalAlignment = -4108
            wb.SHEETS(1).CELLS(rng, i).VerticalAlignment = -4108
            wb.SHEETS(1).CELLS(rng, i).WrapText = True
            wb.SHEETS(1).CELLS(rng, i).Orientation = 0
            wb.SHEETS(1).CELLS(rng, i).AddIndent = False
            wb.SHEETS(1).CELLS(rng, i).IndentLevel = 0
            wb.SHEETS(1).CELLS(rng, i).ShrinkToFit = False
            wb.SHEETS(1).CELLS(rng, i).MergeCells = False
            wb.SHEETS(1).CELLS(rng, i).ReadingOrder = -5002
        Next
        rng = rng + 1

        Dim Cod_Vehiculo As String = ""
        Dim Liq_Optocontrol As Double = 0

        Do While rdBuscar.Read()
            Cod_Vehiculo = BuscarStrDatoCitra("SELECT Cod_Vehiculo FROM Autobuses WHERE NoEconomico='" & rdBuscar("NoEconomico").ToString.Trim & "'")
            Liq_Optocontrol = BuscarDblDatoOptoControl("SELECT SUM(MONTO_MENOS_DEDUCCIONES) AS MONTO_MENOS_DEDUCCIONES FROM HISTORICO_RECAUDACION H INNER JOIN VEHICULO V ON H.ID_VEHICULO=V.ID_VEHICULO WHERE V.COD_VEHICULO='" & Cod_Vehiculo & "' AND CONVERT(VARCHAR,H.FECHA,101)='" & strFecha1 & "'")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("NoEconomico")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("Pasajeros")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Monto")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("Marcas")
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("Pasajeros") - rdBuscar("Marcas")
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("Deducciones")
            wb.SHEETS(1).cells(rng, 9).value = rdBuscar("Neto")
            wb.SHEETS(1).cells(rng, 10).value = Liq_Optocontrol
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("Neto") - Liq_Optocontrol
            rng = rng + 1
        Loop
       
        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(2, 2).value = "Reporte de Liquidacion del " & Me.datFecha.Value.ToString("d")
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        
        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.SHEETS(1).columns(2).columnwidth = 15
        wb.SHEETS(1).columns(3).columnwidth = 30
        wb.SHEETS(1).columns(4).columnwidth = 10
        wb.SHEETS(1).columns(5).columnwidth = 12
        wb.SHEETS(1).columns(6).columnwidth = 12
        wb.SHEETS(1).columns(7).columnwidth = 12
        wb.SHEETS(1).columns(8).columnwidth = 12
        wb.SHEETS(1).columns(9).columnwidth = 12
        wb.SHEETS(1).columns(10).columnwidth = 12
        wb.SHEETS(1).columns(11).columnwidth = 12
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696

      



    End Sub
   
    Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub

    Private Sub ProcesarConParametrosDeOptocontrolToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProcesarConParametrosDeOptocontrolToolStripMenuItem.Click
        Dim Autobus As String = lstAutobuses.Text

        If Len(Autobus) = 0 Then
            MsgBox("No se ha seleccionado ningun autobus", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If


        Dim R As New clsRecaudacion
        R.IdAutobus = BuscarIntDatoCitra("SELECT IdAutobus FROM Autobuses WHERE NoEconomico='" & lstAutobuses.Text & "'")
        R.IdServicio = BuscarIntDatoCitra("SELECT IdServicio FROM RolesDescuentos WHERE Nombre='" & lstRoles.Text & "'")
        R.IdOperador = BuscarIntDatoCitra("SELECT IdOperador FROM OperadoresGlobal WHERE Nombre='" & dgOptocontrol.CurrentRow.Cells(8).Value & "'")
        R.Fecha = datFecha.Value
        R.Pasajeros = dgOptocontrol.CurrentRow.Cells(5).Value
        R.Monto = dgOptocontrol.CurrentRow.Cells(6).Value

        Dim PorcentajeMarcas As Double = BuscarIntDatoOptoControl("SELECT PORCENTAJE_MARCAS_DESCUENTO FROM RUTA WHERE COD_RUTA='" & dgOptocontrol.CurrentRow.Cells(9).Value & "'")
        R.MarcasDescontadas = R.Pasajeros * (PorcentajeMarcas / 100)
        R.MarcasRedondeadas = Int(R.MarcasDescontadas)

        Dim PorcentajeDescuento As Double = BuscarIntDatoOptoControl("SELECT PORCENTAJE_TARIFA_DESCUENTO FROM RUTA WHERE COD_RUTA='" & dgOptocontrol.CurrentRow.Cells(9).Value & "'")
        Dim Tarifa As Double = dgOptocontrol.CurrentRow.Cells(7).Value
        R.MontoDeducciones = (R.MarcasDescontadas * Tarifa) * (PorcentajeDescuento / 100)
        R.MontoMenosDeducciones = R.Monto - R.MontoDeducciones
        R.Insertar()

        CargaRecaudacionDia()
    End Sub
End Class